
<script src="http://malsup.github.com/jquery.form.js"></script> 
<script type="text/javascript">

 	function HandleFileButtonClick() {
  		document.frmUpload.myFile.click();
 	}
 
 	function FileSelect() {
		document.frmUpload.txtFakeText.value = document.frmUpload.myFile.value;
 	}
	
	function FormPressed() { 
			
    	var fd = new FormData();
    	var file = $('#myFile').get(0).files[0];
    	fd.append("myFile", file);
			
		$.ajax({
      		type: 'POST',
      		cache: false,
      		url: "../upload/_index.php",
      		processData: false,
      		contentType: false,
      		data: fd,
      		success: function(data){
		
				$("#content-detail").html(data);
     		},
            beforeSend: function(){
                
                $("#load").show();
            },
            complete: function(){
                
                $("#load").hide();
            }
    	});
	}
	
	function AlarmReveal(content) {
		
		$('#dialog-confirm #dialog-content').text(content);
		
		console.log($("#dropdown-2 ul").html());
		$('#dialog-confirm').dialog({
				resizable: false,
				height:200,
				modal: true,
				buttons: {
        			"OK": function() {
          				$( this ).dialog( "close" );
					}
				}
		});
	}

</script>

<link href="../style.css" rel="stylesheet" type="text/css" />
<!----- Dialog Confirm ------>
<div id="dialog-confirm" title="Alert" hidden="YES">
  <p><span class="ui-icon ui-icon-alert" style="float: left; margin: 0 7px 20px 0;"></span><div id="dialog-content">Delete data. Are you sure?</div></p>
</div>

<div class="container2">

<section id="content-upload">

	<div class="login-upload">

  	  <p>Upload Data</p>

<?php	 


		include_once('../config.php'); 

		session_start();

		LinkDataBase();
	  	include('../Classes/PHPExcel.php');

		include('../Classes/PHPExcel/IOFactory.php'); 

		

		if(!empty($_FILES['myFile'])){

			if($_FILES['myFile']['name'] != ""){
				$uploaddir = '../tmp/';

				$uploadfile = $uploaddir.$_SESSION['ID'].basename($_FILES['myFile']['name']);

				if(strpos(basename($_FILES['myFile']['name']), ".xlsx")){

					if (move_uploaded_file($_FILES['myFile']['tmp_name'], $uploadfile)) {
						
						$objReader = PHPExcel_IOFactory::createReader('Excel2007');

						$objReader->setReadDataOnly(true);

						$objPHPExcel = $objReader->load($uploadfile);

						$Total_Table = $objPHPExcel->getSheetCount();
						
						$allSheetName = $objPHPExcel->getSheetNames();

						$TableCounter = 0;
						
						$Chart_index = 0;

						while($TableCounter < $Total_Table) {

							$objPHPExcel-> setActiveSheetIndex($TableCounter);

							$objWorksheet = $objPHPExcel->getActiveSheet();

							$highestRow = $objWorksheet->getHighestRow(); //Catch All Rows.

							$highestColumn = $objWorksheet->getHighestColumn();

							$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
							
							$charttitle = $objWorksheet->getCellByColumnAndRow(1, 1)->getValue();
							
							if(!isset($TableSeason)) 
							{
								$TempArray = explode(' ',$objWorksheet->getCellByColumnAndRow(0, 12)->getValue());
								$TableSeason = $TempArray[1];
							}
							
							$TableYear = substr(str_replace('[','',$objWorksheet->getCellByColumnAndRow(0, 13)->getValue()),0,4);
							
							//Catch second column.
							if ($objWorksheet->getCellByColumnAndRow(2, 2)->getValue() != "") {
								
								//Catch chart title, type, category, etc.
								$Array_Chart[$Chart_index]['charttitle'] = str_replace("'", "''", $objWorksheet->getCellByColumnAndRow(1, 1)->getValue());
								$Array_Chart[$Chart_index]['charttype'] = str_replace("'", "''", $objWorksheet->getCellByColumnAndRow(2, 2)->getValue());
								$Array_Chart[$Chart_index]['category'] = str_replace("'", "''", $objWorksheet->getCellByColumnAndRow(2, 3)->getValue());
								$Array_Chart[$Chart_index]['sub_category'] = str_replace("'", "''", $objWorksheet->getCellByColumnAndRow(2, 4)->getValue());
								
								//Process location index.
								if ($objWorksheet->getCellByColumnAndRow(2, 5)->getValue() != '')
									$locationIndex = $objWorksheet->getCellByColumnAndRow(2, 5)->getValue();
								else
									$locationIndex = 999;
									
								$Array_Chart[$Chart_index]['locationindex'] = $locationIndex;
								$Array_Chart[$Chart_index]['previous_qa'] = $objWorksheet->getCellByColumnAndRow(2, 6)->getValue();
								$Array_Chart[$Chart_index]['net_only'] = $objWorksheet->getCellByColumnAndRow(2, 7)->getValue();
								
								//Process x-axis.
								if ($objWorksheet->getCellByColumnAndRow(2, 8)->getValue() != '')
									$Array_Chart[$Chart_index]['x_axis'] = $objWorksheet->getCellByColumnAndRow(2, 8)->getValue();
								else
									$Array_Chart[$Chart_index]['x_axis'] = 'Category';
								
								$Array_Chart[$Chart_index]['table_number'] = $allSheetName[$TableCounter];
								
								$Chart_index++;
							}
							
							$TableCounter++;
						}
						
						//Add data into database for charts.
						for ($index=0;$index<count($Array_Chart);$index++) {
							
							$chart_title = $Array_Chart[$index]['charttitle'];
							$chart_type = $Array_Chart[$index]['charttype'];
							$category = $Array_Chart[$index]['category'];
							$sub_category =	$Array_Chart[$index]['sub_category'];
							$location_index	= $Array_Chart[$index]['locationindex'];
							$previous_qa = $Array_Chart[$index]['previous_qa'];
							$net_only = $Array_Chart[$index]['net_only'];
							$x_axis = $Array_Chart[$index]['x_axis'];
							$table_number =	$Array_Chart[$index]['table_number'];
							
							$keyword = '(Net)';
							$contain = 'NO';
							if ($net_only!='') 
								$contain = 'YES';
							
							if ($location_index=='')
								$location_index = 1;
							
							//For sheetreveal_first.
							if ($previous_qa=='') {
								
								if ($sub_category=='')
									$sql = "SELECT id FROM droplist WHERE year = ".$TableYear." AND season = '".$TableSeason."' AND name = '".$category."'";
								else
									$sql = "SELECT id FROM droplist WHERE year = ".$TableYear." AND season = '".$TableSeason."' AND name = '".$category."' AND sub_name = '".$sub_category."'";
								
								$result_droplist = mysql_query($sql);
								
								$numResult_droplist = mysql_num_rows($result_droplist);
								
								if($numResult_droplist==0) {
									if ($sub_category=='')
										$sql="INSERT INTO droplist (year, season, name) VALUES (".$TableYear.", '".$TableSeason."', '".$category."')";
									else 
										$sql="INSERT INTO droplist (year, season, name, sub_name) VALUES (".$TableYear.", '".$TableSeason."', '".$category."', '".$sub_category."')";
									mysql_query($sql);
									if (mysql_insert_id()!=0)
										$droplist_id = mysql_insert_id();
								}
								else {
									$row = mysql_fetch_assoc($result_droplist);
									$droplist_id = $row['id'];
								}
								
								$sql = "SELECT id FROM sheetreveal_first WHERE droplist_id =".$droplist_id." AND table_number = '".$table_number."'";
								$result_sheetreveal_first = mysql_query($sql);
								$numResult_sheetreveal = mysql_num_rows($result_sheetreveal_first);
								
								//Check dabebase benn had same row.
								if ($numResult_sheetreveal==0) {
								
									$sql="INSERT INTO sheetreveal_first (droplist_id, location_index, table_number, charttitle, charttype, keyword, contain, x_axis) 
											VALUES (".$droplist_id.", ".$location_index.", '".$table_number."', '".$chart_title."', '".$chart_type."','".$keyword."', '".$contain."', '".$x_axis."')";
									mysql_query($sql);
								}
							}
							//For sheetreveal_second, sheetreveal_third, etc.
							else {
								
								$sql = "SELECT id FROM sheetreveal_first WHERE table_number = '".$previous_qa."'";
								$result_sheetreveal_first_2 = mysql_query($sql);
								$numResult_sheetreveal = mysql_num_rows($result_sheetreveal_first_2);
								
								//Search sheetreveal_first.
								if ($numResult_sheetreveal!=0) {
									
									$row_first = mysql_fetch_assoc($result_sheetreveal_first_2);
									$sheetreveal_first_id = $row_first['id'];
									
									$sql = "SELECT id FROM sheetreveal_second WHERE table_number = '".$table_number."'";
									$result_sheetreveal_second = mysql_query($sql);
									$numResult_sheetreveal = mysql_num_rows($result_sheetreveal_second);
									
									//Check dabebase benn had same row.
									if ($numResult_sheetreveal==0) {
										
										$sql="INSERT INTO sheetreveal_second (sheetreveal_first_id, location_index, table_number, charttitle, charttype, keyword, contain, x_axis) 
												VALUES (".$sheetreveal_first_id.", ".$location_index.", '".$table_number."', '".$chart_title."', '".$chart_type."','".$keyword."', '".$contain."', '".$x_axis."')";
										mysql_query($sql);
									}
								}
								//Search sheetreveal_second.
								else {
										
									$sql = "SELECT id FROM sheetreveal_second WHERE table_number = '".$previous_qa."'";
									$result_sheetreveal_second_2 = mysql_query($sql);
									$numResult_sheetreveal = mysql_num_rows($result_sheetreveal_second_2);
									
									//Search sheetreveal_second.
									if ($numResult_sheetreveal!=0) {
										
										$row = mysql_fetch_assoc($result_sheetreveal_second_2);
										$sheetreveal_second_id = $row['id'];
										
										$sql = "SELECT id FROM sheetreveal_third WHERE table_number = '".$table_number."'";
										$result_sheetreveal_thrid = mysql_query($sql);
										$numResult_sheetreveal = mysql_num_rows($result_sheetreveal_thrid);
										
										//Check dabebase benn had same row.
										if ($numResult_sheetreveal==0) {
											
											$sql="INSERT INTO sheetreveal_third (sheetreveal_second_id, location_index, table_number, charttitle, charttype, keyword, contain, x_axis) 
													VALUES (".$sheetreveal_second_id.", ".$location_index.", '".$table_number."', '".$chart_title."', '".$chart_type."','".$keyword."', '".$contain."', '".$x_axis."')";
											mysql_query($sql);
										}
									}
								}
							}
						}
						unlink($uploadfile);
						
						echo 'Your Data has been successfully added.';
					}

					else
						echo 'Upload Failed';
				}

				else{

					if($numResult!=0)
						echo 'File Existed';
					else
						echo 'Your Data format was not correct.';
				}

			}

			else{

				//null

			}

		}			

     ?>

	  	<p class="login-small">Upload  your data files here in .xlsx format.<br />

      To see the sample data format, download the excel file here: </span> <a href="../upload/Spring2013DatabaseFile.xlsx"class="login-small-link"><u>SampleData.xlsx</u></a></p>

    </div>

	  <form action="#" name="frmUpload" method="post" enctype="multipart/form-data" onsubmit="FormPressed();return false;">

		<div>

			<input type="document" name="txtFakeText" placeholder="Filename" required id="document" />

            <input type="file" name="myFile" id="myFile" style="display: none" onChange="FileSelect();">

			<span class="nbsp">sadfsf</span>

			<input type="button" class="browse" value="Browse" onclick="HandleFileButtonClick();" />

		</div>

    		<input type="hidden" name="max_file_size" value="2048000">

		<div>

			<input class="white" id="submitfile" type="submit" value="Upload" style="color:black" />

			</div>

    </form>

		</section>

</div>

